package com.aibaixun.web.controller.common;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * 读取第三方数据模型生成本地表
 */
public class ExcelToSql {
    public static void main(String[] args) throws IOException {
        String excelFilePath = "E:\\table.xlsx"; // 替换为你的Excel文件路径
        List<String> createTableSqlList = readExcelAndGenerateSql(excelFilePath);
        for (String sql : createTableSqlList) {
            System.out.println(sql);
        }
    }

    public static List<String> readExcelAndGenerateSql(String excelFilePath) throws IOException {
        List<String> createTableSqlList = new ArrayList<>();
        FileInputStream fis = new FileInputStream(excelFilePath);
        Workbook workbook = new XSSFWorkbook(fis);
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i); // 假设Excel文件只有一个工作表
            String tableName = sheet.getSheetName();
            StringBuilder columns = new StringBuilder();
            StringBuilder values = new StringBuilder();
            for (int j = 1; j <= sheet.getLastRowNum(); j++) {
                Row row = sheet.getRow(j);
                if (row == null) {
                    continue;
                }
                columns.append(row.getCell(0).getStringCellValue()).append(" ")
                    .append(getColumnType(row.getCell(0))).append(" ").append("COMMENT '"+row.getCell(1).getStringCellValue()+"'");
                values.append("'").append(row.getCell(0).getStringCellValue()).append("'");
                columns.append(", ");
                values.append(", ");
            }
            createTableSqlList.add(String.format("CREATE TABLE %s (%s)", tableName
                , columns.substring(0,columns.length()-2).toString().trim(), values.substring(0,values.length()-2).toString().trim())+";");
        }
        workbook.close();
        fis.close();
        return createTableSqlList;
    }

    private static String getColumnType(Cell cell) {
        // 根据实际情况判断列类型，这里只是一个示例
        if (cell.getCellType() == CellType.STRING) {
            return "VARCHAR(50)";
        } else if (cell.getCellType() == CellType.NUMERIC) {
            return "INT";
        } else {
            return "VARCHAR(50)";
        }
    }
}

